﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 人员管理3
{

    internal class Person
    {
        public long ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Sex { get; set; }
        public string Addr { get; set; }

    }

    internal class PersonUtils
    {
        private static readonly string constr = "Data Source=.;Initial Catalog=test;User Id=sa;Password=root123;";


        internal IEnumerable<Person> SelectAll()
        {
            using (var connection = new SqlConnection(constr))
            {
                var persons = connection.Query<Person>("SELECT id,name,age,sex,addr FROM Person order by id asc ");
                return persons;
            }
        }

        internal bool EditPerson(long selId, string name, string age, string sex, string addr)
        {
            bool b = false;

            Person person = new Person() 
            { 
                ID = selId,
                Name = name,
                Addr = addr,
                Age = Convert.ToInt32(age), 
                Sex = sex
            
            };

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("UPDATE Person SET name = @name,age = @age,sex = @sex,addr = @addr WHERE Id = @Id", person);
                b = v > 0;
            }
            return b;
        }

        internal bool DeletePerson(long selId)
        {
            bool b = false;

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("DELETE FROM Person WHERE Id = @Id", new {Id = selId});
                b = v > 0;
            }
            return b;
        }

        internal bool InsertPerson(string name, string age, string sex, string addr)
        {
            bool b = false;

            Person person = new Person()
            {
                Name = name,
                Addr = addr,
                Age = Convert.ToInt32(age),
                Sex = sex

            };

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("INSERT INTO Person (name,age,sex,addr) VALUES (@name, @age,@sex,@addr)", person);
                b = v > 0;
            }
            return b;

        }

        /// <summary>
        /// 查询人员列表
        /// </summary>
        /// <param name="name"></param>
        /// <param name="age"></param>
        /// <param name="sex"></param>
        /// <param name="addr"></param>
        /// <returns></returns>
        internal IEnumerable<Person> FindPersons(string name, string age, string sex, string addr)
        {
            using (var connection = new SqlConnection(constr))
            {
                StringBuilder sb = new StringBuilder(" SELECT id,name,age,sex,addr FROM Person ");
                sb.Append(" where 1=1 ");
                
                if(name != null && name.Trim().Length > 1)
                {
                    sb.Append($" and name like '%{name.Trim()}%' ");
                }

                if(age != null && age.Trim().Length > 1)
                {
                    sb.Append($" and age='{age.Trim()}' ");
                }

                if (sex != null && sex.Trim().Length > 1)
                {
                    sb.Append($" and sex='{sex.Trim()}' ");
                }

                if (addr != null && addr.Trim().Length > 1)
                {
                    sb.Append($" and addr='{addr.Trim()}' ");
                }

                sb.Append(" order by id asc ");

                var persons = connection.Query<Person>(sb.ToString());
                return persons;
            }
        }
    }
}
